package org.lq.shopping.dao.impl;

import java.math.BigDecimal;
import java.sql.Date;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;
import org.lq.shopping.dao.ShopStockDao;
import org.lq.shopping.entity.ShopStock;
import org.lq.shopping.util.DruidUtil;

public class ShopStockDaoImpl implements ShopStockDao {

	QueryRunner qr = new QueryRunner(DruidUtil.getDataSource());
	Logger log = Logger.getLogger(ShopStockDaoImpl.class);
	@Override
	public int add(ShopStock t) {
		int num = 0;
		String sql = "insert into shop_stock\n" +
				"  (stock_id, goods_id, stock_num, stock_time, stock_person)\n" + 
				"values\n" + 
				"  (stock_seq.nextval, ?, ?, sysdate, ?)";
		log.info("这是添加入库信息的方法!");
		log.debug(sql);
		try {
			num = qr.execute(sql, t.getGoods_id(),t.getStock_num(),t.getStock_person());
		} catch (SQLException e) {
			log.error("错误:添加入库信息失败!");
		}
		return num;
	}

	@Override
	public int delete(int id) {
		int num = 0;
		String sql = 
				"delete shop_stock\n" +
						" where stock_id = ?";
		log.info("这是删除入库信息的方法!");
		log.debug(sql);
		try {
			num = qr.execute(sql, id);
		} catch (SQLException e) {
			log.error("错误:删除入库信息失败!");
		}
		return num;
	}

	@Override
	public int update(ShopStock t) {
		int num = 0;
		String sql = "update shop_stock\n" +
				"   set goods_id = ?,\n" + 
				"       stock_num = ?,\n" + 
				"       stock_time = sysdate,\n" + 
				"       stock_person = ?\n" + 
				" where stock_id = ?";
		log.info("这是修改入库信息的方法!");
		log.debug(sql);
		try {
			num = qr.execute(sql, t.getGoods_id(),t.getStock_num(),t.getStock_person(),t.getStock_id());
		} catch (SQLException e) {
			log.error("错误:修改入库信息失败!");
		}
		return num;
	}

	@Override
	public List<ShopStock> findAll() {
		List<ShopStock> list = null;
		String sql = "select * from shop_stock order by stock_id";
		log.info("这是查询所有入库信息的方法!");
		log.debug(sql);
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopStock.class));
		} catch (SQLException e) {
			log.error("错误:查询所有入库信息失败!");
		}
		return list;
	}

	@Override
	public ShopStock findById(int id) {
		ShopStock ss = null;
		String sql = "select * from shop_stock where stock_id = ?";
		log.info("这是根据id查询入库信息的方法!");
		log.debug(sql);
		try {
			ss = qr.query(sql, new BeanHandler<>(ShopStock.class),id);
		} catch (SQLException e) {
			log.error("错误:根据id查询入库信息失败!");
		}
		return ss;
	}

	@Override
	public List<ShopStock> getStockByPersonName(String name,int pageIndex,int pageSize) {
		List<ShopStock> list = null;
		String sql = "select * from ("
				+ "select rownum r,t.* from("
				+ "select * from shop_stock where stock_person = ? order by stock_id) t "
				+ "where rownum <=? )"
				+ "where r>?";
		int max = pageIndex * pageSize;
		int min = (pageIndex-1)*pageSize;
		log.info("这是根据入库人查询入库信息的方法!");
		log.debug(sql);
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopStock.class),name,max,min);
		} catch (SQLException e) {
			log.error("错误:查询入库信息失败!");
		}
		return list;
	}

	@Override
	public List<ShopStock> getStockBetweenDate(Date beginTime, Date endTime,int pageIndex,int pageSize) {
		List<ShopStock> list = null;
		String sql = "select * from ("
				+ "select rownum r,t.* from("
				+ "select * from shop_stock where stock_time between ? and ? order by stock_id) t "
				+ "where rownum <=? )"
				+ "where r>?";
		int max = pageIndex * pageSize;
		int min = (pageIndex-1)*pageSize;
		log.info("这是根据时间区间查询入库信息的方法!");
		log.debug(sql);
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopStock.class),beginTime,endTime,max,min);
		} catch (SQLException e) {
			log.error("错误:查询入库信息失败!");
		}
		return list;
	}

	@Override
	public List<ShopStock> LikeColumnByValue(String columName, Object value) {
		List<ShopStock> list =null;
		String sql ="select * from shop_stock where ? like ?";
		log.info("这是根据输入的值查询列的方法!");
		log.debug(sql);
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopStock.class),columName,"%"+value+"%");
		} catch (SQLException e) {
			log.error("错误:查询入库信息失败!");
		}
		return list;
	}

	@Override
	public int getCount() {
		int num =0;
		String sql ="select count(1) from shop_stock";
		log.info("这是获取入库信息表数据条数的方法!");
		log.debug(sql);
		try {
			num = qr.query(sql, new ScalarHandler<BigDecimal>()).intValue();
		} catch (SQLException e) {
			log.error("错误:查询入库信息数目失败!");
		}
		return num;
	}

	@Override
	public List<ShopStock> findPage(int pageIndex, int pageSize) {
		List<ShopStock> list =null;
		String sql ="select * from ("
				+ "select rownum r,t.* from("
				+ "select * from shop_stock order by stock_id) t "
				+ "where rownum <=? )"
				+ "where r>?";
		int max = pageIndex * pageSize;
		int min = (pageIndex-1)*pageSize;
		log.info("这是入库信息的分页方法!");
		log.debug(sql);
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopStock.class),max,min);
		} catch (SQLException e) {
			log.error("错误:查询入库分页信息失败!");
		}
		return list;
	}

	@Override
	public List<ShopStock> getStockPersonName(String name) {
		List<ShopStock> list = null;
		String sql = "select * from shop_stock where stock_person = ? order by stock_id";
		log.info("这是根据入库人查询入库信息的方法!");
		log.debug(sql);
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopStock.class),name);
		} catch (SQLException e) {
			log.error("错误:查询入库信息失败!");
		}
		return list;
	}

	@Override
	public List<ShopStock> getBetweenDate(Date beginTime, Date endTime) {
		List<ShopStock> list = null;
		String sql = "select * from shop_stock where stock_time between ? and ? order by stock_id";
		log.info("这是根据时间区间入库信息的方法!");
		log.debug(sql);
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopStock.class),beginTime,endTime);
		} catch (SQLException e) {
			log.error("错误:根据时间区间查询入库信息失败!");
		}
		return list;
	}

}
